import sqlite3
import threading
from datetime import datetime
import os

class Database:
    _instance = None
    _lock = threading.Lock()
    
    def __new__(cls):
        with cls._lock:
            if cls._instance is None:
                cls._instance = super(Database, cls).__new__(cls)
                cls._instance._initialized = False
            return cls._instance
    
    def __init__(self):
        if not self._initialized:
            self.db_name = "hrms.db"
            self.conn_pool = {}
            self.create_tables()
            self._initialized = True
    
    def get_connection(self):
        """获取数据库连接（线程安全的连接池）"""
        thread_id = threading.get_ident()
        if thread_id not in self.conn_pool:
            try:
                conn = sqlite3.connect(
                    self.db_name, 
                    check_same_thread=False,
                    timeout=30
                )
                conn.execute("PRAGMA foreign_keys = ON")
                conn.execute("PRAGMA journal_mode = WAL")
                self.conn_pool[thread_id] = conn
            except Exception as e:
                print(f"数据库连接失败: {e}")
                raise
        return self.conn_pool[thread_id]
    
    def close_connections(self):
        """关闭所有数据库连接"""
        for thread_id, conn in self.conn_pool.items():
            try:
                conn.close()
            except Exception as e:
                print(f"关闭数据库连接失败: {e}")
        self.conn_pool.clear()
    
    def create_tables(self):
        """创建数据表"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 检查表是否存在
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='employees'")
            table_exists = cursor.fetchone()
            
            if not table_exists:
                # 员工表
                cursor.execute('''
                    CREATE TABLE employees (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL,
                        gender TEXT,
                        id_card TEXT UNIQUE,
                        birth_date DATE,
                        nation TEXT,
                        political_status TEXT,
                        phone TEXT,
                        school TEXT,
                        department TEXT,
                        position TEXT,
                        job_title TEXT,
                        hire_date DATE,
                        work_year INTEGER DEFAULT 0,
                        is_active BOOLEAN DEFAULT 1,
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 请假表
                cursor.execute('''
                    CREATE TABLE leaves (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        employee_id INTEGER,
                        employee_name TEXT,
                        department TEXT,
                        leave_type TEXT,
                        start_date DATE,
                        end_date DATE,
                        days INTEGER,
                        reason TEXT,
                        status TEXT DEFAULT 'pending',
                        approver TEXT,
                        approve_time DATETIME,
                        cancel_reason TEXT,
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE
                    )
                ''')
                
                # 创建索引
                cursor.execute('CREATE INDEX idx_employees_name ON employees(name)')
                cursor.execute('CREATE INDEX idx_employees_department ON employees(department)')
                cursor.execute('CREATE INDEX idx_employees_is_active ON employees(is_active)')
                cursor.execute('CREATE INDEX idx_leaves_employee_id ON leaves(employee_id)')
                cursor.execute('CREATE INDEX idx_leaves_status ON leaves(status)')
                
                conn.commit()
                print("数据表和索引创建成功")
                
        except Exception as e:
            print(f"创建数据表失败: {e}")
            conn.rollback()
    
    def execute_query(self, query, params=()):
        """执行查询并返回结果"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            cursor.execute(query, params)
            result = cursor.fetchall()
            return result
        except Exception as e:
            print(f"查询执行失败: {e}")
            return []
    
    def execute_update(self, query, params=()):
        """执行更新操作"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            return True
        except sqlite3.IntegrityError as e:
            print(f"数据完整性错误: {e}")
            conn.rollback()
            return False
        except Exception as e:
            print(f"更新操作失败: {e}")
            conn.rollback()
            return False
    
    def add_employee(self, employee_data):
        """添加员工"""
        check_query = "SELECT id FROM employees WHERE id_card = ? AND is_active = 1"
        existing = self.execute_query(check_query, (employee_data[2],))
        
        if existing:
            return False
        
        query = '''
            INSERT INTO employees (name, gender, id_card, birth_date, nation, 
            political_status, phone, school, department, position, job_title, hire_date)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        '''
        return self.execute_update(query, employee_data)
    
    def delete_employee(self, employee_id):
        """删除员工（逻辑删除）"""
        query = "UPDATE employees SET is_active = 0 WHERE id = ?"
        return self.execute_update(query, (employee_id,))
    
    def batch_delete_employees(self, employee_ids):
        """批量删除员工（逻辑删除）"""
        if not employee_ids:
            return True
            
        placeholders = ','.join(['?'] * len(employee_ids))
        query = f"UPDATE employees SET is_active = 0 WHERE id IN ({placeholders})"
        return self.execute_update(query, employee_ids)
    
    def get_all_employees(self):
        """获取所有员工"""
        query = "SELECT * FROM employees WHERE is_active = 1 ORDER BY name"
        return self.execute_query(query)
    
    def get_employee_by_id(self, employee_id):
        """根据ID获取员工"""
        query = "SELECT * FROM employees WHERE id = ? AND is_active = 1"
        result = self.execute_query(query, (employee_id,))
        return result[0] if result else None
    
    # def search_employees(self, search_text):
    #     """搜索员工"""
    #     query = '''
    #         SELECT * FROM employees 
    #         WHERE is_active = 1 AND (name LIKE ? OR department LIKE ? OR position LIKE ? OR id_card LIKE ?)
    #         ORDER BY name
    #     '''
    #     search_pattern = f'%{search_text}%'
    #     return self.execute_query(query, (search_pattern, search_pattern, search_pattern, search_pattern))    

    def add_employee(self, employee_data):
        """添加员工"""
        # 先检查是否已存在（包括已删除的）
        check_query = "SELECT id, is_active FROM employees WHERE id_card = ?"
        existing = self.execute_query(check_query, (employee_data[2],))
        
        if existing:
            employee_id, is_active = existing[0]
            # 如果已存在且是已删除状态，则更新记录而不是新增
            if not is_active:
                update_query = '''
                    UPDATE employees SET 
                    name=?, gender=?, birth_date=?, nation=?, political_status=?,
                    phone=?, school=?, department=?, position=?, job_title=?, hire_date=?, is_active=1
                    WHERE id=?
                '''
                update_data = employee_data[:2] + employee_data[3:] + (employee_id,)
                return self.execute_update(update_query, update_data)
            else:
                # 已存在且是活跃状态，不允许重复添加
                return False
        
        # 新增员工
        query = '''
            INSERT INTO employees (name, gender, id_card, birth_date, nation, 
            political_status, phone, school, department, position, job_title, hire_date)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        '''
        return self.execute_update(query, employee_data)

    def add_leave(self, leave_data):
        """添加请假申请"""
        query = '''
            INSERT INTO leaves (employee_id, employee_name, department, 
            leave_type, start_date, end_date, days, reason)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        '''
        return self.execute_update(query, leave_data)
    
    def get_all_leaves(self):
        """获取所有请假记录"""
        query = "SELECT * FROM leaves ORDER BY created_time DESC"
        return self.execute_query(query)
    
    def get_leaves_by_employee(self, employee_id):
        """根据员工ID获取请假记录"""
        query = "SELECT * FROM leaves WHERE employee_id = ? ORDER BY created_time DESC"
        return self.execute_query(query, (employee_id,))
    
    def update_leave_status(self, leave_id, status, approver):
        """更新请假状态"""
        query = "UPDATE leaves SET status = ?, approver = ?, approve_time = datetime('now') WHERE id = ?"
        return self.execute_update(query, (status, approver, leave_id))
    
    def delete_leave(self, leave_id):
        """删除请假记录"""
        query = "DELETE FROM leaves WHERE id = ?"
        return self.execute_update(query, (leave_id,))
    
    def batch_delete_leaves(self, leave_ids):
        """批量删除请假记录"""
        if not leave_ids:
            return True
            
        placeholders = ','.join(['?'] * len(leave_ids))
        query = f"DELETE FROM leaves WHERE id IN ({placeholders})"
        return self.execute_update(query, leave_ids)
    
    def get_leave_stats(self):
        """获取请假统计"""
        query = '''
            SELECT school, department, leave_type, COUNT(*), SUM(days)
            FROM leaves 
            WHERE status = 'approved'
            GROUP BY school, department, leave_type
        '''
        return self.execute_query(query)
    
    def get_employees_by_ids(self, employee_ids):
        """根据ID列表获取员工信息"""
        if not employee_ids:
            return []
            
        placeholders = ','.join(['?'] * len(employee_ids))
        query = f"SELECT * FROM employees WHERE id IN ({placeholders}) AND is_active = 1"
        return self.execute_query(query, employee_ids)
    
    def get_leaves_by_ids(self, leave_ids):
        """根据ID列表获取请假信息"""
        if not leave_ids:
            return []
            
        placeholders = ','.join(['?'] * len(leave_ids))
        query = f"SELECT * FROM leaves WHERE id IN ({placeholders})"
        return self.execute_query(query, leave_ids)